﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace MpSoft.Basic.DataAccess
{
    /// <summary>
    /// Summary description for dbHelp
    /// </summary>
    public class DbHelp
    {
        #region Var

        private static string _ConnectString = System.Configuration
            .ConfigurationManager.ConnectionStrings["dbConnectionString"].ToString();
        private static SqlConnection _connection = null;
        private static SqlDataReader _datareader = null;
        private static SqlTransaction Transaction = null;
        private static SqlCommand command = null;

        #endregion

        #region Properties

        public static string ConnectString { get { return _ConnectString; } }

        #endregion

        #region Menthod

        /// <summary>
        /// phân trang 
        /// </summary>
        /// <param name="Reader"></param>
        /// <returns>put chi dung cho khi minh dung table</returns>
        public static SqlDataReader DataReader(string NameOutPut, out string TotalRow, SqlCommand command)
        {
            _connection = new SqlConnection(ConnectString);
            _connection.Open();
            command.Connection = _connection;
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters[NameOutPut].Direction = ParameterDirection.Output;
            _datareader = command.ExecuteReader(CommandBehavior.CloseConnection);
            TotalRow = command.Parameters[NameOutPut].Value.ToString();
            return _datareader;
        }
        // sqldatareader
        public static SqlDataReader DataReader(SqlCommand command)
        {
            _connection = new SqlConnection(ConnectString);
            _connection.Open();
            command.Connection = _connection;
            command.CommandType = CommandType.StoredProcedure;
            _datareader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return _datareader;
        }

        // data set
        public static DataSet ExecuteDataset(string tableName, string procName, params SqlParameter[] parameters)
        {

            SqlCommand command = new SqlCommand();
            command.CommandText = procName;
            if (parameters != null)
            {
                for (int i = 0; i < parameters.Length; i++)
                    command.Parameters.Add(parameters[i]);
                return ExecuteDataset(tableName, procName, command);
            }
            return null;
        }
        public static DataSet ExecuteDataset(string tableName, string procName, SqlCommand command)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(ConnectString);
                conn.Open();
                command.Connection = conn;
                command.CommandText = procName;
                command.CommandType = CommandType.StoredProcedure;
                adapter = new SqlDataAdapter(command);
                adapter.Fill(ds, tableName);
            }
            catch (SqlException)
            {
                throw;
            }
            finally
            {
                if (conn != null)
                    conn.Dispose();
                conn.Close();
            }
            return ds;
        }

        // thực hiện câu lệnh update, insert, delete

        public static int ExecuteNonQuery(SqlCommand command)
        {
            int result = -1;


            using (_connection = new SqlConnection(ConnectString))
            {
                _connection.Open();                             // mở kết nối với csdl
                Transaction = _connection.BeginTransaction();   // khởi tạo transaction
                command.Connection = _connection;               // gán kết nối cho command
                command.CommandType = CommandType.StoredProcedure; // lệnh kiểu

                try
                {
                    command.Transaction = Transaction;          // gán transaction
                    result = command.ExecuteNonQuery();         // thực hiện câu lệnh
                    Transaction.Commit();                       // sau khi hoàn thiện thì nó commit
                }
                catch //(SqlException ex)
                {
                    Transaction.Rollback();                     // nếu sảy ra lỗi thì quay lại
                    //System.Web.HttpContext.Current.Response.Write(ex.Message);
                }
                finally
                {
                    Diponse();                          // giải phóng tất cả
                }
            }
            return result;
        }

        public static int FieldsUpdateExcuteNonQuyery(string proName, string FieldID, string FieldValue, List<int> IDList, List<string> ValueList)
        {
            int result = -1;

            using (_connection = new SqlConnection(ConnectString))
            {

                _connection.Open();                             // mở kết nối với csdl
                Transaction = _connection.BeginTransaction();   // khởi tạo transaction
               


                try
                {
                    if (IDList.Count == ValueList.Count && IDList.Count > 0)
                    {
                        for (int i = 0; i < IDList.Count; i++)
                        {
                            command = new SqlCommand();                     // tạo sql Command
                            command.Connection = _connection;               // gán kết nối cho command
                            command.CommandText = proName;                  // tên store cần dùng
                            command.CommandType = CommandType.StoredProcedure; // lệnh kiểu

                            command.Parameters.Add(CreateParameter(FieldID, IDList[i]));
                            command.Parameters.Add(CreateParameter(FieldValue, ValueList[i]));

                            command.Transaction = Transaction;          // gán transaction
                            command.ExecuteNonQuery();         // thực hiện câu lệnh

                        }
                        Transaction.Commit();
                    }
                    result = 1;
                    // sau khi hoàn thiện thì nó commit
                }
                catch (SqlException ex)
                {
                    Transaction.Rollback();                     // nếu sảy ra lỗi thì quay lại
                    //System.Web.HttpContext.Current.Response.Write(ex.Message);
                }
                finally
                {
                    Diponse();                          // giải phóng tất cả
                }
            }
            return result;
        }

        public static int FieldsDeleteExcuteNonQuyery(string proName, string FieldID, List<int> IDList)
        {
            int result = -1;


            using (_connection = new SqlConnection(ConnectString))
            {

                _connection.Open();                             // mở kết nối với csdl
                Transaction = _connection.BeginTransaction();   // khởi tạo transaction

                try
                {
                    if (IDList.Count > 0)
                    {


                        for (int i = 0; i < IDList.Count; i++)
                        {
                            command = new SqlCommand();                     // tạo sql Command
                            command.Connection = _connection;               // gán kết nối cho command
                            command.Transaction = Transaction;   // gán transaction
                            command.CommandText = proName;                  // tên store cần dùng
                            command.CommandType = CommandType.StoredProcedure; // lệnh kiểu
                            command.Parameters.Add(CreateParameter(FieldID, IDList[i]));
                            command.ExecuteNonQuery();         // thực hiện câu lệnh

                        }
                        Transaction.Commit();
                    }

                    result = 1;
                    // sau khi hoàn thiện thì nó commit
                }
                catch (SqlException ex)
                {
                    Transaction.Rollback();                     // nếu sảy ra lỗi thì quay lại
                    //System.Web.HttpContext.Current.Response.Write(ex.Message);
                }
                finally
                {
                    Diponse();                          // giải phóng tất cả
                }
            }
            return result;
        }
        // nó sẽ trả về một giá trị cột đầu tiên, giá trị đầu tiên

        public static int ExecuteScalar(SqlCommand command)
        {
            int result = -1;
            using (_connection = new SqlConnection(ConnectString))
            {
                _connection.Open();                              // mở kết nối với csdl
                Transaction = _connection.BeginTransaction();   // khởi tạo transaction
                command.Connection = _connection;               // gán kết nối cho command
                command.CommandType = CommandType.StoredProcedure;  // lệnh kiểu
                try
                {
                    command.Transaction = Transaction;                      // gán transaction
                    var value = command.ExecuteScalar().ToString();
                    if (value != null)
                        int.TryParse(value.ToString(), out result);
                    Transaction.Commit();                                   // thực hiện xong
                }
                catch (SqlException ex)
                {
                    Transaction.Rollback();                                 // nếu sảy ra lỗi thì quay lại
                  //  System.Web.HttpContext.Current.Response.Write(ex.Message);
                }
                finally
                {
                    Diponse();                                  // giải phóng tất cả    
                }
            }
            return result;
        }
        // Create Prameter

        public static SqlParameter CreateParameter(string paramName, object value)
        {
            SqlParameter parameter = new SqlParameter(paramName, value);
            return parameter;
        }

        /// <summary>
        /// Dispose 
        /// </summary>

        public static void Diponse()
        {
            if (_datareader != null)
            {
                _datareader.Close();
                _datareader.Dispose();
            }
            if (_connection != null)
            {
                _connection.Close();
                _connection.Dispose();
            }
            if (Transaction != null)
                Transaction.Dispose();
            if (command != null)
                command.Dispose();
        }

        #endregion
    }
}
